package com.common.db;

import java.io.File;
import java.io.FileInputStream;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Map;
import java.util.Properties;
import java.util.Set;
import java.util.HashMap;
import java.util.concurrent.atomic.AtomicBoolean;

import javax.naming.Binding;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NameClassPair;
import javax.naming.NamingEnumeration;
import javax.sql.DataSource;

import org.apache.commons.dbcp.ConnectionFactory;
import org.apache.commons.dbcp.DriverManagerConnectionFactory;
import org.apache.commons.dbcp.PoolableConnectionFactory;
import org.apache.commons.dbcp.PoolingDriver;
import org.apache.commons.pool.ObjectPool;
import org.apache.commons.pool.impl.GenericObjectPool;

import com.common.shared.ClSelectionItem;
import com.common.shared.map.GisLayer;
import com.common.shared.map.GisLayerOptions;
import com.common.shared.map.GisMap;
import com.common.shared.map.GisMapClickedObject;
import com.common.shared.map.GisMapProperties;
import com.common.shared.map.GisMap_Building;
import com.common.shared.map.GisMap_Roads;
import com.common.shared.model.Classifier;
import com.common.shared.model.ClassifierType;
import com.common.shared.usermanager.Group;
import com.common.shared.usermanager.Permission;
import com.common.shared.usermanager.PermitionTable;
import com.common.shared.usermanager.User;
import com.common.shared.usermanager.UserGroup;

public class DBConnection {

	private static class PooledConnection {
		public long creatitonTime = System.currentTimeMillis() + 60000;
		public Connection connection;
	}

	private static final String POOLEDCONNECTIONP_REFIX = "pooledConnection.";
	private static final String POOLEDCONNECTIONP_URL = "jdbc:apache:commons:dbcp:"
			+ POOLEDCONNECTIONP_REFIX;

	private static ObjectPool connectionPool;

	private static final AtomicBoolean inited = new AtomicBoolean(false);
	private static boolean cannotUseIsomorphic = false;
	private static Method getconnectionMethod;
	private static Method getfreeconnection;

	private static int openedConnections = 0;
	private static int closedConnections = 0;

	private static Map<Integer, PooledConnection> mapConnections = new HashMap<Integer, PooledConnection>();

	public static void checkActiveConnections() {
		long time = System.currentTimeMillis();
		Set<Integer> keys = mapConnections.keySet();
		for (Integer key : keys) {
			PooledConnection pc = mapConnections.get(key);
			if (pc.creatitonTime > time)
				try {
					connectionCounts();
					System.err
							.println("Was unclosed connections more than 5 seconds, closing it!!!");
					freeConnection(pc.connection);

				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}

	}

	private static Properties fillPropertie(String key, Properties props) {
		Properties ret = new Properties();
		Set<Object> keys = props.keySet();
		for (Object elem : keys) {
			String dbkey = elem.toString();
			if (dbkey.startsWith(key)) {
				String val = props.getProperty(dbkey);
				dbkey = dbkey.substring(key.length() + 1);
				ret.put(dbkey, val);
			}
		}
		return ret;

	}

	private static void initConnections() throws Exception {
		File file = new File("DB.properties");
		System.out.println("Searching DB Configuration in "
				+ file.getAbsolutePath());
		Properties props = new Properties();
		try {
			props.load(new FileInputStream(file));
		} catch (Exception ex) {
		}
		Set<Object> keys = props.keySet();
		HashMap<Object, Properties> propertieMap = new HashMap<Object, Properties>();
		for (Object elem : keys) {
			String dbkey = elem.toString();
			if (dbkey.startsWith("db.")) {
				String val = props.getProperty(dbkey);
				propertieMap.put(val, fillPropertie(val, props));
			}
		}

		keys = propertieMap.keySet();
		Class.forName("org.apache.commons.dbcp.PoolingDriver");
		for (Object elem : keys) {
			String dbkey = elem.toString();
			props = propertieMap.get(dbkey);
			String connectURI = props.getProperty("connectURI");
			connectionPool = new GenericObjectPool(null);
			ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(
					connectURI, props);
			new PoolableConnectionFactory(connectionFactory, connectionPool,
					null, null, false, true);

			PoolingDriver driver = (PoolingDriver) DriverManager
					.getDriver("jdbc:apache:commons:dbcp:");
			driver.registerPool(POOLEDCONNECTIONP_REFIX + dbkey, connectionPool);

		}
	}

	private static final void connectionCounts() {
		// System.err.println("Opened Connections=" + openedConnections
		// + " Closed connections=" + closedConnections);
	}

	public static final void freeConnection(Connection conn) throws Exception {
		if (conn == null)
			return;
		// if (!cannotUseIsomorphic) {
		// try {
		// getfreeconnection.invoke(null, conn);
		// } catch (Exception e) {
		// // TODO: handle exception
		// }
		//
		// try {
		// conn.close();
		// } catch (Exception e) {
		// // TODO: handle exception
		// }
		// } else
		try {
			conn.rollback();
		} catch (Exception e) {
			// TODO: handle exception
		}
		conn.close();
		closedConnections++;
		connectionCounts();
		int hc = conn.hashCode();
		mapConnections.remove(hc);

	}

	private static void putConnection(Connection conn) {
		int hc = conn.hashCode();
		PooledConnection pc = new PooledConnection();
		mapConnections.put(hc, pc);
		openedConnections++;
		connectionCounts();
	}

	public static final HashMap<String, String> mapJni = new HashMap<String, String>();

	public static Connection getConnection(String dbName) throws Exception {

		// String jni = mapJni.get(dbName);
		// if (jni != null) {
		// try {
		// Context initContext = new InitialContext();
		// System.out.println("listing initContext");
		// list(initContext);
		// Context envContext = (Context) initContext.lookup("");
		// System.out.println("listing envContext");
		// list(envContext);
		// DataSource ds = null;
		// System.out.println("attempting to connect to =java:" + jni);
		// try {
		// ds = (DataSource) envContext.lookup("java:" + jni);
		// } catch (Exception e) {
		// System.out.println("failed attempt to connect to =" + jni);
		// ds = (DataSource) envContext.lookup(jni);
		// }
		// Connection conn = ds.getConnection();
		// if (conn.getAutoCommit())
		// conn.setAutoCommit(false);
		// putConnection(conn);
		// return conn;
		// } catch (Exception e) {
		//
		// }
		// }

		if (!cannotUseIsomorphic) {
			try {
				if (getconnectionMethod == null) {
					try {
						Class<?> cl = Class
								.forName("com.isomorphic.sql.SQLConnectionManager");
						getconnectionMethod = cl.getDeclaredMethod(
								"getNewConnection", String.class);
						getfreeconnection = cl.getDeclaredMethod(
								"freeConnection", Connection.class);
					} catch (Exception e) {
						cannotUseIsomorphic = true;
					}

				}
				if (!cannotUseIsomorphic) {
					Connection conn = (Connection) getconnectionMethod.invoke(
							null, dbName);
					if (conn.getAutoCommit())
						conn.setAutoCommit(false);
					putConnection(conn);

					return conn;
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		if (!inited.get()) {
			initConnections();
			inited.set(true);
		}
		Connection con = DriverManager.getConnection(POOLEDCONNECTIONP_URL
				+ dbName);
		con.setAutoCommit(false);
		putConnection(con);
		return con;
	}

	public static User loginUser(String userName, String password,
			String dbName, Connection conn) throws Exception {
		User result = null;
		String sql = "select id, user_name,caption_id from susers where user_name=? and pwd=md5(md5(?))  limit 1";
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = conn == null ? getConnection(dbName) : conn;
			stmt = con.prepareStatement(sql);
			stmt.setString(1, userName.trim());
			stmt.setString(2, password);
			rs = stmt.executeQuery();
			if (rs.next()) {
				result = DBClassMapping.getUser(rs);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				rs.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
			try {
				stmt.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
			try {
				if (conn == null)
					DBConnection.freeConnection(con);
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}

		return result;
	}

	public static ArrayList<User> getAllUsers(String dbName, Connection conn)
			throws Exception {
		ArrayList<User> result = new ArrayList<User>();
		String sql = "select id, user_name,caption_id from susers ";
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = conn == null ? getConnection(dbName) : conn;
			stmt = con.prepareStatement(sql);

			rs = stmt.executeQuery();
			while (rs.next()) {
				result.add(DBClassMapping.getUser(rs));
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				rs.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
			try {
				stmt.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
			try {
				if (conn == null)
					DBConnection.freeConnection(con);
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}

		return result;
	}

	public static ArrayList<Group> getAllGroups(String dbName, Connection conn)
			throws Exception {
		ArrayList<Group> result = new ArrayList<Group>();
		String sql = "select id, group_name,caption_id from sgroups ";
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = conn == null ? getConnection(dbName) : conn;
			stmt = con.prepareStatement(sql);

			rs = stmt.executeQuery();
			while (rs.next()) {
				result.add(DBClassMapping.getGroup(rs));
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				rs.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
			try {
				stmt.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
			try {
				if (conn == null)
					DBConnection.freeConnection(con);
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}

		return result;
	}

	public static ArrayList<Permission> getAllPermisions(String dbName,
			Connection conn) throws Exception {
		ArrayList<Permission> result = new ArrayList<Permission>();
		String sql = "select id, permission_name,caption_id from spermitions ";
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = conn == null ? getConnection(dbName) : conn;
			stmt = con.prepareStatement(sql);

			rs = stmt.executeQuery();
			while (rs.next()) {
				result.add(DBClassMapping.getPermition(rs));
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				rs.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
			try {
				stmt.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
			try {
				if (conn == null)
					DBConnection.freeConnection(con);
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}

		return result;
	}

	public static ArrayList<PermitionTable> getAllPermitionTables(
			String dbName, Connection conn) throws Exception {
		ArrayList<PermitionTable> result = new ArrayList<PermitionTable>();
		String sql = "select permition_id, user_or_group_id,userperm,accepted from permition_table ";
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = conn == null ? getConnection(dbName) : conn;
			stmt = con.prepareStatement(sql);

			rs = stmt.executeQuery();
			while (rs.next()) {
				result.add(DBClassMapping.getPermitionTable(rs));
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				rs.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
			try {
				stmt.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
			try {
				if (conn == null)
					DBConnection.freeConnection(con);
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}

		return result;
	}

	public static ArrayList<UserGroup> getAllUserGroups(String dbName,
			Connection conn) throws Exception {
		ArrayList<UserGroup> result = new ArrayList<UserGroup>();
		String sql = "select user_id, group_id from user_groups ";
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = conn == null ? getConnection(dbName) : conn;
			stmt = con.prepareStatement(sql);

			rs = stmt.executeQuery();
			while (rs.next()) {
				result.add(DBClassMapping.getUserGroup(rs));
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				rs.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
			try {
				stmt.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
			try {
				if (conn == null)
					DBConnection.freeConnection(con);
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}

		return result;
	}

	public static GisMap_Roads getGisMap_Roads(long road_id, String dbName)
			throws Exception {
		GisMap_Roads result = null;
		String sql = "select gid, street,raioni,regioni,city from geo_roads where gid="
				+ road_id;
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			con = getConnection(dbName);
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);
			if (rs.next()) {
				result = DBClassMapping.getGisMap_Roads(rs);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				rs.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
			try {
				stmt.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
			try {
				DBConnection.freeConnection(con);
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}

		return result;
	}

	public static GisMap_Building getGisMap_Building(long building_id,
			String dbName) throws Exception {
		GisMap_Building result = null;
		String sql = "select gid, street,raioni,regioni,city,home from geo_buildings where gid="
				+ building_id;
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			con = getConnection(dbName);
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);
			if (rs.next()) {
				result = DBClassMapping.getGisMap_Building(rs);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				rs.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
			try {
				stmt.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
			try {
				DBConnection.freeConnection(con);
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}

		return result;
	}

	public static ArrayList<ClSelectionItem> getSelectionItems(String baseName,
			String sql, boolean withParent, Connection connn) throws Exception {
		ArrayList<ClSelectionItem> result = new ArrayList<ClSelectionItem>();

		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			con = connn == null ? getConnection(baseName) : connn;
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				result.add(DBClassMapping.getClSelectionItem(rs, withParent));
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				rs.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
			try {
				stmt.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
			try {
				if (connn == null)
					DBConnection.freeConnection(con);
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}

		return result;
	}

	public static HashMap<Integer, ArrayList<Classifier>> getClassifiersForTypes(
			int[] types, String dbName) throws Exception {
		HashMap<Integer, ArrayList<Classifier>> HashMap = new HashMap<Integer, ArrayList<Classifier>>();
		String stypes = "";
		for (int i = 0; i < types.length; i++) {
			if (i != 0)
				stypes += ",";
			stypes += types[i];
		}
		if (types.length != 0)
			stypes = " where cltype in (" + stypes + ")";
		String sql = "select id, cltype, typeid, typename from classificator "
				+ stypes + " order by cltype,typeid";
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			con = getConnection(dbName);
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				Classifier cl = DBClassMapping.getClassifier(rs);
				ArrayList<Classifier> cls = HashMap.get(cl.getCltype());
				if (cls == null) {
					cls = new ArrayList<Classifier>();
					HashMap.put(cl.getCltype(), cls);
				}
				cls.add(cl);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				rs.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
			try {
				stmt.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
			try {
				DBConnection.freeConnection(con);
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}

		return HashMap;
	}

	public static ArrayList<GisMapClickedObject> getGisMapClickedObjects(
			double px, double py, int srid, String dbName) throws Exception {
		ArrayList<GisMapClickedObject> result = new ArrayList<GisMapClickedObject>();
		String sql = "select gid, obj_type , obj_text, gis_astext,raioni , regioni,sstreet,city from getObjectInfo(?,?,?)";
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = DBConnection.getConnection(dbName);
			stmt = con.prepareStatement(sql);
			stmt.setDouble(1, px);
			stmt.setDouble(2, py);
			stmt.setInt(3, srid);
			rs = stmt.executeQuery();
			while (rs.next()) {
				result.add(DBClassMapping.getGisMapClickedObject(rs));
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage() + " aaa"
					+ e.getClass().toString() + "");
		} finally {
			try {
				rs.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
			try {
				stmt.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
			try {
				DBConnection.freeConnection(con);
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}

		return result;
	}

	public static GisMapClickedObject getBuilding(long building_id,
			String dbName) throws Exception {
		GisMapClickedObject result = null;
		String sql = "select gid, 1 obj_type , '' obj_text, astext(the_geom) gis_astext,raioni , regioni,street sstreet,city from geo_buildings where gid="
				+ building_id;
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = DBConnection.getConnection(dbName);
			stmt = con.prepareStatement(sql);

			rs = stmt.executeQuery();
			if (rs.next()) {
				result = DBClassMapping.getGisMapClickedObject(rs);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				rs.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
			try {
				stmt.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
			try {
				DBConnection.freeConnection(con);
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}

		return result;
	}

	public static ArrayList<ClassifierType> getClassifierTypes(String dbName)
			throws Exception {
		ArrayList<ClassifierType> result = new ArrayList<ClassifierType>();
		String sql = "select id, classifictypename from classifiertype order by id";
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			con = getConnection(dbName);
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				result.add(DBClassMapping.getClassifierType(rs));
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				rs.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
			try {
				stmt.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
			try {
				DBConnection.freeConnection(con);
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}

		return result;
	}

	public static ArrayList<GisMap> getMaps(String dbName, Connection conn)
			throws Exception {
		ArrayList<GisMap> result = new ArrayList<GisMap>();
		String sql = "select id, mapname, zoomto, width, height from gismap order by id limit 1";
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			con = conn == null ? getConnection(dbName) : conn;
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				result.add(DBClassMapping.getMap(rs));
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				rs.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
			try {
				stmt.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
			try {
				if (conn == null)
					DBConnection.freeConnection(con);
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}

		return result;
	}

	public static HashMap<Integer, HashMap<Integer, ArrayList<ClSelectionItem>>> getStatuses(
			String dbName, Connection conn) throws Exception {
		HashMap<Integer, HashMap<Integer, ArrayList<ClSelectionItem>>> result = new HashMap<Integer, HashMap<Integer, ArrayList<ClSelectionItem>>>();
		String sql = "select id, statuslang, docstatuscaptionvalue, system_id from doc_system_status_v ds order by statuslang,system_id,id";
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			con = conn == null ? getConnection(dbName) : conn;
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				ClSelectionItem item = new ClSelectionItem();
				item.setId(rs.getLong("id"));
				item.setValue(rs.getString("docstatuscaptionvalue"));
				Integer language_id = rs.getInt("statuslang");
				Integer system_id = rs.getInt("system_id");
				HashMap<Integer, ArrayList<ClSelectionItem>> systemst = result
						.get(language_id);
				if (systemst == null) {
					systemst = new HashMap<Integer, ArrayList<ClSelectionItem>>();
					result.put(language_id, systemst);
				}
				ArrayList<ClSelectionItem> items = systemst.get(system_id);
				if (items == null) {
					items = new ArrayList<ClSelectionItem>();
					systemst.put(system_id, items);
				}
				items.add(item);

			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				rs.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
			try {
				stmt.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
			try {
				if (conn == null)
					DBConnection.freeConnection(con);
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}

		return result;
	}

	public static ArrayList<GisMapProperties> getGisMapProperties(
			String dbName, Connection conn) throws Exception {
		ArrayList<GisMapProperties> result = new ArrayList<GisMapProperties>();
		String sql = "select id, gismap_id, propertie_name, propertie_value,ptype from gismap_properties order by gismap_id, id";
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			con = conn == null ? getConnection(dbName) : conn;
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				result.add(DBClassMapping.getGisMapProperties(rs));
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				rs.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
			try {
				stmt.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
			try {
				if (conn == null)
					DBConnection.freeConnection(con);
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}

		return result;
	}

	public static ArrayList<GisLayer> getGisLayers(String dbName,
			Connection conn) throws Exception {
		ArrayList<GisLayer> result = new ArrayList<GisLayer>();
		String sql = "select id, gismap_id, layername,title,iswms,priority,baselayer,gmaptype,sphericalmercator,wmsurl from gis_layer order by gismap_id, priority";
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			con = conn == null ? getConnection(dbName) : conn;
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				result.add(DBClassMapping.getGisLayer(rs));
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				rs.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
			try {
				stmt.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
			try {
				if (conn == null)
					DBConnection.freeConnection(con);
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}

		return result;
	}

	public static ArrayList<GisLayerOptions> getGisLayerOptions(String dbName,
			Connection conn) throws Exception {
		ArrayList<GisLayerOptions> result = new ArrayList<GisLayerOptions>();
		String sql = "select id, gis_layer_id, propertie_name, propertie_value,ptype from gis_layer_properties order by gis_layer_id, id";
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			con = conn == null ? getConnection(dbName) : conn;
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				result.add(DBClassMapping.getGisLayerOptions(rs));
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				rs.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
			try {
				stmt.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
			try {
				if (conn == null)
					DBConnection.freeConnection(con);
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}

		return result;
	}

	public static int[] addEditClisifier(Classifier cl,
			DoSomeOtherOperation otherOperation, String dbName)
			throws Exception {
		int[] result = null;
		String sql = "select addeditclassificator(?,?,?)";
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = getConnection(dbName);
			stmt = con.prepareStatement(sql);
			stmt.setInt(1, cl.getCltype());
			stmt.setInt(2, cl.getTypeid());
			stmt.setString(3, cl.getTypename());
			rs = stmt.executeQuery();
			if (rs.next()) {
				String obj = rs.getString(1);
				String objs[] = obj.split(",");
				int p1 = Integer.parseInt(objs[0].substring(1));
				int p2 = Integer.parseInt(objs[1].substring(0,
						objs[1].length() - 1));
				result = new int[] { p1, p2 };
			}
			if (otherOperation != null)
				otherOperation.doSomeOtherDBOperation(con, result);
			con.commit();
		} catch (Exception e) {
			try {
				con.rollback();
			} catch (Exception e2) {

			}
			throw new Exception(e.getMessage());
		} finally {
			try {
				rs.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
			try {
				stmt.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
			try {
				DBConnection.freeConnection(con);
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}

		return result;
	}

	public static void editClisifierType(ClassifierType clt, String dbName)
			throws Exception {

		String sql = "update classifiertype set classifictypename = ? where id=?";
		Connection con = null;
		PreparedStatement stmt = null;

		try {
			con = getConnection(dbName);
			stmt = con.prepareStatement(sql);
			stmt.setString(1, clt.getClassifictypename());
			stmt.setInt(2, clt.getId());
			stmt.execute();
			con.commit();
		} catch (Exception e) {
			try {
				con.rollback();
			} catch (Exception e2) {
				// TODO: handle exception
			}
			throw new Exception(e.getMessage());
		} finally {
			try {
				stmt.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
			try {
				DBConnection.freeConnection(con);
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}

	}
}
